package com.aixin.csopsupplier.repository;

import com.aixin.csopsupplier.domain.SupplierDef;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.transaction.annotation.Transactional;


public interface SupplierDefRepository extends JpaRepository<SupplierDef,Long> {

    @Query(value = "SELECT DISTINCT sd.supplierID,sd.supplierName,sd.totalOfService,sd.remarks,sd.startTime,sd.endTime,sd.status,sd.createTime,sd.updateTime,sd.userName,sd.password  from supplierdef  sd left join servicedef servicedef on sd.supplierID = servicedef.supplierID where sd.status < 2 " +
            " and (:supplierName is  null or sd.supplierName = :supplierName) " +
            " and (:serviceID is  null or servicedef.serviceID = :serviceID) ",nativeQuery = true)
    Page<SupplierDef> getSupplierDef(@Param("supplierName") String supplierName, @Param("serviceID") String serviceID, Pageable pageable);


    @Modifying
    @Query(value ="UPDATE  supplierdef s SET s.supplierName = :supplierName, s.remarks= :remarks,"
            + "s.startTime = :startTime,s.endTime= :endTime,s.status =:status,s.updateTime =:updateTime where s.supplierID =:supplierID" ,nativeQuery = true)
    int updateSupplierDef(@Param("supplierName") String supplierName,@Param("remarks") String remarks,@Param("startTime") String startTime,
                           @Param("endTime") String endTime ,@Param("status") int status ,@Param("supplierID") String supplierID ,@Param("updateTime") String updateTime);



    @Query(value = "SELECT * from supplierdef   WHERE  userName =:userName and password=:password " ,nativeQuery = true)
    SupplierDef supplierLogin(@Param("userName") String userName,@Param("password") String password);

    @Modifying
    @Transactional
    @Query(value ="UPDATE  supplierdef  SET password =:password where supplierID =:supplierID" ,nativeQuery = true)
    int updatePassword(@Param("supplierID") String supplierID,@Param("password") String supplierPassword);



}
